﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.BasicList
{
    public partial class panel_Part : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;
        
        public panel_Part()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            LoadData();

            this.Part.DoubleClick += Part_DoubleClick;
            this.Part.PopupMenuShowing += Part_PopupMenuShowing;
            this.partContext.ItemClicked += partContext_ItemClicked;
        }

        private void callEditForm()
        {
            DataRow row = Part.GetFocusedDataRow();
            frm_Edit_Part editForm = new frm_Edit_Part();
            editForm.part_no = row[0].ToString();
            if (editForm.LoadData())
            {
                //editForm.Show();
                editForm.ShowDialog();
                //LoadData();
            }
            else
            {
                MessageBox.Show("Cannot load data for selected record");
            }
        }

        void Part_DoubleClick(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        void partContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.partContext.Hide();            
            switch (e.ClickedItem.Name)
            {
                case "importSelection":
                    importDatabase();
                    break;
                case "importExcelSelection":
                    importExcel();
                    break;
                case "addSelection":
                    frm_Add_Part add_form = new frm_Add_Part();
                    add_form.ShowDialog();
                    //LoadData();
                    break;
                case "editSelection":
                    callEditForm();                    
                    break;
                case "deleteSelection":
                    deleteRecord();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void Part_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.partContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
            ModuleWaitDialog.SetWaitDialogCaption("Loading data...");                    

            DataSet sourceDataSet = new DataSet();            
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT t1.[PartNo], t1.[PartNoPurchase], t1.[PartEnglishName], t1.[PartName], t1.[PartVietNamName],"
                + " t2.FullName, t3.EnglishName, t4.SupplierName, t5.SupplierName, t1.[IQID],"
                + " LTRIM(RTRIM(t1.[UsingUnit])) AS UsingUnit, LTRIM(RTRIM(t1.[BuyingUnit])) AS BuyingUnit,"
                + " t7.UnitID AS IQUnit, t1.[Using/IQEx], t1.[Buying/IQEx],t1.[LeadTime], t1.[MinQuantity],"
                + " t1.[ROHS], t1.[Material], t1.[Weight], t1.[PartSize], t1.[QuantityBasePrice], t1.[ExportOrImport],"
                + " t1.[SafeQuantity], t1.[PType], t1.[Class], t1.[PackingNote], t1.[Remark], t1.[Marume], t6.[CountryName], t1.Department"
                + " FROM [222_Part] AS t1 LEFT OUTER JOIN"
                + " [222_Customer] AS t2 ON t1.CustomerID=t2.CustomerID LEFT OUTER JOIN"
                + " [222_Maker] AS t3 ON t1.MakerID=t3.MakerID LEFT OUTER JOIN"
                + " [222_Supplier] AS t4 ON t1.SupplierID=t4.SupplierID LEFT OUTER JOIN"
                + " [222_Supplier] AS t5 ON t1.SecondSupplierID=t5.SupplierID LEFT OUTER JOIN"
                + " [222_Country] AS t6 ON t1.[OCountry]=t6.CountryCode LEFT OUTER JOIN"
                + " [222_IQ] AS t7 ON t1.[IQID] = t7.[IQID]"
                + " ORDER BY t1.[PartNo]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                foreach (DataRow part_row in sourceDataSet.Tables[0].Rows)
                {
                    if (!string.IsNullOrEmpty(part_row["UsingUnit"].ToString().Trim()))
                    {
                        if (((int)(part_row["UsingUnit"].ToString().Trim()[0])) == 65402)
                        {                           
                            part_row.BeginEdit();
                            part_row["UsingUnit"] = "PC";
                            part_row.EndEdit();
                        }
                    }

                    if (!string.IsNullOrEmpty(part_row["BuyingUnit"].ToString().Trim()))
                    {
                        if (((int)(part_row["BuyingUnit"].ToString().Trim()[0])) == 65402)
                        {
                            part_row.BeginEdit();
                            part_row["BuyingUnit"] = "PC";
                            part_row.EndEdit();
                        }
                    }
                }
                gridPart.DataSource = sourceDataSet.Tables[0];                                
            }
            else
            {
                MessageBox.Show("There is no data for Part");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("PartNo", typeof(int));
                newTable.Columns.Add("PartNoPurchase", typeof(string));
                newTable.Columns.Add("PartEnglishName", typeof(string));
                newTable.Columns.Add("PartName", typeof(string));
                newTable.Columns.Add("PartVietNamName", typeof(string));
                newTable.Columns.Add("Customer", typeof(string));
                newTable.Columns.Add("Maker", typeof(string));
                newTable.Columns.Add("Supplier", typeof(string));
                newTable.Columns.Add("SecondSupplier", typeof(string));
                newTable.Columns.Add("IQID", typeof(string));
                newTable.Columns.Add("UsingUnit", typeof(string));
                newTable.Columns.Add("BuyingUnit", typeof(string));
                newTable.Columns.Add("IQUnit", typeof(string));
                newTable.Columns.Add("Using/IQEx", typeof(string));
                newTable.Columns.Add("Buying/IQEx", typeof(string));
                newTable.Columns.Add("LeadTime", typeof(string));
                newTable.Columns.Add("MinQuantity", typeof(string));
                newTable.Columns.Add("ROHS", typeof(string));
                newTable.Columns.Add("Material", typeof(string));
                newTable.Columns.Add("Weight", typeof(string));
                newTable.Columns.Add("PartSize", typeof(string));
                newTable.Columns.Add("QuantityBasePrice", typeof(string));
                newTable.Columns.Add("ExportOrImport", typeof(string));
                newTable.Columns.Add("SafeQuantity", typeof(string));
                newTable.Columns.Add("PType", typeof(string));
                newTable.Columns.Add("Class", typeof(string));
                newTable.Columns.Add("PackingNote", typeof(string));
                newTable.Columns.Add("Remark", typeof(string));
                newTable.Columns.Add("Marume", typeof(string));
                newTable.Columns.Add("OCountry", typeof(string));
                newTable.Columns.Add("Department", typeof(string));
                gridPart.DataSource = newTable;
            }
            Part.Columns[0].Caption = "Part No";
            Part.Columns[1].Caption = "Part No Purchase";
            Part.Columns[2].Caption = "Part English Name";
            Part.Columns[3].Caption = "Part Name";
            Part.Columns[4].Caption = "Part VietNam Name";
            Part.Columns[5].Caption = "Customer";
            Part.Columns[6].Caption = "Maker";
            Part.Columns[7].Caption = "Supplier";
            Part.Columns[8].Caption = "Second Supplier";
            Part.Columns[9].Caption = "IQID";
            Part.Columns[10].Caption = "Using Unit";
            Part.Columns[11].Caption = "Buying Unit";
            Part.Columns[12].Caption = "IQ Unit";
            Part.Columns[13].Caption = "Using/IQ Exchange";
            Part.Columns[14].Caption = "Buying/IQ Exchange";
            Part.Columns[15].Caption = "Lead Time";
            Part.Columns[16].Caption = "Min Quantity";
            Part.Columns[17].Caption = "ROHS";
            Part.Columns[18].Caption = "Material";
            Part.Columns[19].Caption = "Weight";
            Part.Columns[20].Caption = "Part Size";
            Part.Columns[21].Caption = "Quantity BasePrice";
            Part.Columns[22].Caption = "ExportOrImport";
            Part.Columns[23].Caption = "Safe Quantity";
            Part.Columns[24].Caption = "PType";
            Part.Columns[25].Caption = "Class";
            Part.Columns[26].Caption = "Packing Note";
            Part.Columns[27].Caption = "Remark";
            Part.Columns[28].Caption = "Marume";
            Part.Columns[29].Caption = "OCountry";
            Part.Columns[30].Caption = "Department";
            Part.BestFitColumns();
            ModuleWaitDialog.CloseWaitDialog();
            getUpdateInfo();
        }

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=2";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information: " + ex.Message);
            }
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }


        private void buttonEdit_Click(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }
        
        ////////////////////////////////////
        /// Update database From AS400
        ////////////////////////////////////

        private void importDatabase()
        {                   
            try
            {
                // Update Data From AS400                
                myConnection.Open();
                ModuleWaitDialog.CreateWaitDialog("", "Please waiting...");
                ModuleWaitDialog.SetWaitDialogCaption("Updating Part list from AS400...");

                string sql = "UPDATE t1 SET t1.[PartNo] = t2.[PARTNO],"
                    + " t1.[PartNoPurchase] = t2.[PARTNOPURCHASE],"
                    + " t1.[PartEnglishName] = t2.[PARTENGLISHNAME],"
                    + " t1.[PartName] = t2.[PARTNAME],"
                    + " t1.[MakerID] = t2.[MAKERID], t1.[SupplierID] = t2.[SUPPLIERID], t1.Remark = t2.[REMARK],"
                    + " t1.[SecondSupplierID] = t2.[SECONDSUPPLIERID], t1.[IQID] = t2.[IQID], t1.[BuyingUnit] = t2.[BUYINGUNIT],"
                    + " t1.[UsingUnit] =t2.[USINGUNIT], t1.[Using/IQEx] = t2.[EXCHANGEUNIT], t1.[LeadTime] = t2.[LEADTIME],"
                    + " t1.[MinQuantity] = t2.[MOQ], t1.[PType] = t2.[PARTTYPE], t1.[Marume] = t2.[MARUME]"
                    + " FROM [222_Part] AS t1 INNER JOIN [VPart] AS t2 ON t1.[PartNo] = t2.[PARTNO] COLLATE Vietnamese_CI_AS";
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                cmd.CommandTimeout = 30000;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                // Insert new Data From AS400
                ModuleWaitDialog.SetWaitDialogCaption("Inserting Part list from AS400...");

                sql = "INSERT INTO [222_Part] ([PartNo], [PartNoPurchase], [PartEnglishName], [PartName], [MakerID],"
                    + " [SupplierID], [SecondSupplierID], [IQID], [BuyingUnit], [UsingUnit], [Using/IQEx], [LeadTime],"
                    + " [MinQuantity], [PType], [Marume], [Remark]) SELECT [PARTNO],"
                    + " [PARTNOPURCHASE], [PARTENGLISHNAME],"
                    + " [PARTNAME], [MAKERID], [SUPPLIERID], [SECONDSUPPLIERID], [IQID], [BUYINGUNIT], [USINGUNIT],"
                    + " [EXCHANGEUNIT], [LEADTIME], [MOQ], [PARTTYPE], [MARUME], [REMARK]"
                    + " FROM [VPart] WHERE [PARTNO] COLLATE Vietnamese_CI_AS NOT IN (SELECT [PartNo] FROM [222_Part])";
                cmd = new SqlCommand(sql, myConnection);
                cmd.CommandTimeout = 30000;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                // Update Unit Exchange
                ModuleWaitDialog.SetWaitDialogCaption("Updating Unit Exchange...");

                sql = "UPDATE t1 SET t1.[BuyingUnit] = t2.[BuyingUnit],"
                    + " t1.[UsingUnit] =t2.[UsingUnit], t1.[Using/IQEx] = t2.[Using/IQEx],"
                    + " t1.[Buying/IQEx] = t2.[Buying/IQEx]"
                    + " FROM [222_Part] AS t1 INNER JOIN [222_PartExchange] AS t2 ON t1.[PartNo]=t2.[PartNo]";
                cmd = new SqlCommand(sql, myConnection);                
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                //Update History
                
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=2";
                cmd = new SqlCommand(sql, myConnection);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                

                myConnection.Close();
                ModuleWaitDialog.CloseWaitDialog();
                MessageBox.Show("Database has been updated successfull.");

                LoadData();
            }
            catch (Exception ex)
            {                
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot connect to AS400");
            } 
        }

        ////////////////////////////////////
        /// Update database From Excel
        ////////////////////////////////////
        private void importExcel()
        {
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();

            bool have_data = false;

            importFile.Filter = "Excel 2007-2013 (.xlsx)|*.xlsx";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("Select [PartNo], [PartNoPurchase], [PartEnglishName],"
                        + " [PartVietNamName], [CustomerID], [MakerID], [SupplierID], [SecondSupplierID], [IQID],"
                        + " [UsingUnit], [BuyingUnit], [Using/IQEx], [Buying/IQEx], [LeadTime], [MinQuantity],"
                        + " [ROHS], [Material], [Weight], [PartSize], [GreenEnvi], [QuantityBasePrice],"
                        + " [ExportOrImport], [SafeQuantity], [PType], [Class], [PackingNote], [Remark],"
                        + " [Marume], [OCountry], [Department], [UpQty]"
                        + " FROM [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);
                    have_data = true;
                    excelConnection.Close();
                }
                catch
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    MessageBox.Show("Cannot import data from file. Please check your file");
                }
            }

            if (have_data)
            {

                DataSet part = new DataSet();
                string sql = null;
                SqlDataAdapter adapter;
                DataTable part_local;
                try
                {
                    //Get Data From Local Database
                    myConnection.Open();
                    sql = "SELECT * FROM [222_Part]";
                    SqlCommand cmd = new SqlCommand(sql, myConnection);
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(part, "222_Part");
                    part_local = part.Tables["222_Part"];
                    int add = 0;
                    int edit = 0;
                    foreach (DataRow excel_row in excel_data.Rows)
                    {
                        DataRow[] current_row = part_local.Select("PartNo='" + excel_row["PartNo"].ToString().Trim() + "'");
                        if (current_row.Length > 0)
                        {
                            foreach (DataRow local_row in current_row)
                            {
                                local_row.BeginEdit();
                                local_row["PartNoPurchase"] = excel_row["PartNoPurchase"].ToString().Trim();
                                local_row["PartEnglishName"] = excel_row["PartEnglishName"].ToString().Trim();
                                local_row["PartVietNamName"] = excel_row["PartVietNamName"].ToString().Trim();
                                local_row["CustomerID"] = excel_row["CustomerID"].ToString().Trim();
                                local_row["MakerID"] = excel_row["MakerID"].ToString().Trim();
                                local_row["SupplierID"] = excel_row["SupplierID"].ToString().Trim();
                                local_row["SecondSupplierID"] = excel_row["SecondSupplierID"].ToString().Trim();
                                local_row["IQID"] = excel_row["IQID"].ToString().Trim();
                                local_row["UsingUnit"] = excel_row["UsingUnit"].ToString().Trim();
                                local_row["BuyingUnit"] = excel_row["BuyingUnit"].ToString().Trim();
                                float usingExchange = 0;
                                if (float.TryParse(excel_row["Using/IQEx"].ToString().Trim(), out usingExchange))
                                {
                                    local_row["Using/IQEx"] = usingExchange;
                                }

                                float buyingExchange = 0;
                                if (float.TryParse(excel_row["Buying/IQEx"].ToString().Trim(), out buyingExchange))
                                {
                                    local_row["Buying/IQEx"] = buyingExchange;
                                }

                                int leadTime = 0;
                                if (Int32.TryParse(excel_row["LeadTime"].ToString().Trim(), out leadTime))
                                {
                                    local_row["LeadTime"] = leadTime;
                                }
                                
                                float minqty = 0;
                                if (float.TryParse(excel_row["MinQuantity"].ToString().Trim(), out minqty))
                                {
                                    local_row["MinQuantity"] = minqty;
                                }
                                
                                int rohs = 0;
                                if (Int32.TryParse(excel_row["ROHS"].ToString().Trim(), out rohs))
                                {
                                    local_row["ROHS"] = rohs;
                                }
                                local_row["Material"] = excel_row["Material"].ToString().Trim();

                                float weight = 0;
                                if (float.TryParse(excel_row["Weight"].ToString().Trim(), out weight))
                                {
                                    local_row["Weight"] = weight;
                                }

                                float size = 0;
                                if (float.TryParse(excel_row["PartSize"].ToString().Trim(), out size))
                                {
                                    local_row["PartSize"] = size;
                                }

                                int greenenvi = 0;
                                if (Int32.TryParse(excel_row["GreenEnvi"].ToString().Trim(), out greenenvi))
                                {
                                    local_row["GreenEnvi"] = greenenvi;
                                }

                                int baseprice = 0;
                                if (Int32.TryParse(excel_row["QuantityBasePrice"].ToString().Trim(), out baseprice))
                                {
                                    local_row["QuantityBasePrice"] = baseprice;
                                }

                                int export = 0;
                                if (Int32.TryParse(excel_row["ExportOrImport"].ToString().Trim(), out export))
                                {
                                    local_row["ExportOrImport"] = export;
                                }

                                float safeqty = 0;
                                if (float.TryParse(excel_row["SafeQuantity"].ToString().Trim(), out safeqty))
                                {
                                    local_row["SafeQuantity"] = safeqty;
                                }
                                local_row["PType"] = excel_row["PType"].ToString().Trim();
                                local_row["Class"] = excel_row["Class"].ToString().Trim();
                                local_row["PackingNote"] = excel_row["PackingNote"].ToString().Trim();
                                local_row["Remark"] = excel_row["Remark"].ToString().Trim();

                                float marume = 0;
                                if (float.TryParse(excel_row["Marume"].ToString().Trim(), out marume))
                                {
                                    local_row["Marume"] = marume;
                                }
                                local_row["OCountry"] = excel_row["OCountry"].ToString().Trim();
                                local_row["Department"] = excel_row["Department"].ToString().Trim();

                                float upqty = 0;
                                if (float.TryParse(excel_row["UpQty"].ToString().Trim(), out upqty))
                                {
                                    local_row["UpQty"] = upqty;
                                }
                                local_row.EndEdit();
                                edit++;
                            }
                        }
                        else
                        {
                            DataRow new_row = part_local.NewRow();
                            new_row["PartNo"] = excel_row["PartNo"].ToString().Trim();
                            new_row["PartNoPurchase"] = excel_row["PartNoPurchase"].ToString().Trim();
                            new_row["PartEnglishName"] = excel_row["PartEnglishName"].ToString().Trim();
                            new_row["PartVietNamName"] = excel_row["PartVietNamName"].ToString().Trim();
                            new_row["CustomerID"] = excel_row["CustomerID"].ToString().Trim();
                            new_row["MakerID"] = excel_row["MakerID"].ToString().Trim();
                            new_row["SupplierID"] = excel_row["SupplierID"].ToString().Trim();
                            new_row["SecondSupplierID"] = excel_row["SecondSupplierID"].ToString().Trim();
                            new_row["IQID"] = excel_row["IQID"].ToString().Trim();
                            new_row["UnitID"] = excel_row["UnitID"].ToString().Trim();
                            new_row["IQUnit"] = excel_row["IQUnit"].ToString().Trim();
                            float unitExchange = 0;
                            if (float.TryParse(excel_row["UnitExchange"].ToString().Trim(), out unitExchange))
                            {
                                new_row["UnitExchange"] = unitExchange;
                            }

                            int leadTime = 0;
                            if (Int32.TryParse(excel_row["LeadTime"].ToString().Trim(), out leadTime))
                            {
                                new_row["LeadTime"] = leadTime;
                            }

                            float minqty = 0;
                            if (float.TryParse(excel_row["MinQuantity"].ToString().Trim(), out minqty))
                            {
                                new_row["MinQuantity"] = minqty;
                            }

                            int rohs = 0;
                            if (Int32.TryParse(excel_row["ROHS"].ToString().Trim(), out rohs))
                            {
                                new_row["ROHS"] = rohs;
                            }                            
                            new_row["Material"] = excel_row["Material"].ToString().Trim();
                            float weight = 0;
                            if (float.TryParse(excel_row["Weight"].ToString().Trim(), out weight))
                            {
                                new_row["Weight"] = weight;
                            }

                            float size = 0;
                            if (float.TryParse(excel_row["PartSize"].ToString().Trim(), out size))
                            {
                                new_row["PartSize"] = size;
                            }

                            int greenenvi = 0;
                            if (Int32.TryParse(excel_row["GreenEnvi"].ToString().Trim(), out greenenvi))
                            {
                                new_row["GreenEnvi"] = greenenvi;
                            }

                            int baseprice = 0;
                            if (Int32.TryParse(excel_row["QuantityBasePrice"].ToString().Trim(), out baseprice))
                            {
                                new_row["QuantityBasePrice"] = baseprice;
                            }

                            int export = 0;
                            if (Int32.TryParse(excel_row["ExportOrImport"].ToString().Trim(), out export))
                            {
                                new_row["ExportOrImport"] = export;
                            }

                            float safeqty = 0;
                            if (float.TryParse(excel_row["SafeQuantity"].ToString().Trim(), out safeqty))
                            {
                                new_row["SafeQuantity"] = safeqty;
                            }                            
                            new_row["PType"] = excel_row["PType"].ToString().Trim();
                            new_row["Class"] = excel_row["Class"].ToString().Trim();
                            new_row["PackingNote"] = excel_row["PackingNote"].ToString().Trim();
                            new_row["Remark"] = excel_row["Remark"].ToString().Trim();

                            float marume = 0;
                            if (float.TryParse(excel_row["Marume"].ToString().Trim(), out marume))
                            {
                                new_row["Marume"] = marume;
                            }
                            new_row["OCountry"] = excel_row["OCountry"].ToString().Trim();
                            new_row["Department"] = excel_row["Department"].ToString().Trim();

                            float upqty = 0;
                            if (float.TryParse(excel_row["UpQty"].ToString().Trim(), out upqty))
                            {
                                new_row["UpQty"] = upqty;
                            }
                            part_local.Rows.Add(new_row);
                            add++;
                        }
                    }


                    SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                    //Update to Local Database
                    adapter.Update(part, "222_Part");

                    adapter.Dispose();
                    cmd.Dispose();
                    myConnection.Close();

                    //Update History
                    myConnection.Open();
                    sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=2";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();

                    LoadData();

                    MessageBox.Show("Database has been updated successfull.");

                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot connect to AS400");
                }
            }
        }

        private void deleteRecord()
        {
            if (MessageBox.Show("Are you really to delete selected record now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                DataRow row = Part.GetFocusedDataRow();
                try
                {
                    myConnection.Open();
                    string sql = "DELETE [222_Part] WHERE PartNo='" + row[0].ToString().Trim() + "'";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();
                    MessageBox.Show("Part deleted success.");
                    LoadData();
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show(ex.Message);
                    MessageBox.Show("Cannot delete Part. Please try again.");
                }
            }
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteRecord();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_Part add_form = new frm_Add_Part();
            add_form.ShowDialog();
            //LoadData();
        }

        private void buttonImportExcel_Click(object sender, EventArgs e)
        {
            importExcel();
        }

        private void buttonExportExcel_Click(object sender, EventArgs e)
        {
            Cursor currentCursor;
            currentCursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                SaveFileDialog saveFDlg = new SaveFileDialog();
                saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                saveFDlg.Title = "Save As";
                saveFDlg.FileName = "ImportQuotation.xlsx";
                if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                {
                    if (!string.IsNullOrEmpty(saveFDlg.FileName))
                    {
                        Part.OptionsPrint.PrintDetails = true;
                        Part.OptionsPrint.ExpandAllDetails = true;
                        Part.OptionsPrint.AutoWidth = true;
                        Part.ExportToXlsx(saveFDlg.FileName);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Warning!");
            }
            finally
            {
                Cursor.Current = currentCursor;
            }
        }
    }
}
